<?php

class m_barang extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    function get_count_all_barang() {
        $sql = "SELECT COUNT(*) 'total' FROM barang a";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    function get_count_all_barang_search($params) {
        $sql = "SELECT COUNT(*) 'total' FROM barang a
                WHERE a.barang_noinventaris LIKE ?
                AND a.barang_nama LIKE ?
                AND a.barang_kondisi LIKE ?
                AND a.barang_status LIKE ?
                AND a.gpbarang_cd LIKE ?
                AND a.jenisbarang_cd LIKE ?
                AND a.kategoribarang_cd LIKE ?
                AND a.merk_cd LIKE ?                
                ";
        
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    //cek data
    function get_count_all_barang_by_kode($params) {
        $sql = "SELECT COUNT(*) 'total' FROM barang WHERE barang_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    function get_count_all_barang_by_pengadaan_id($params) {
        $sql = "SELECT COUNT(*) 'total' FROM barang WHERE pengadaan_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    //cek data
    function get_count_all_barang_by_kode_nama($params) {
        $sql = "SELECT COUNT(*) 'total' FROM barang WHERE barang_id = ? AND barang_nama = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    //cek data
    function get_count_barang_by_kategoribarang_cd($params) {
        $sql = "SELECT COUNT(*) 'total' FROM barang WHERE kategoribarang_cd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }
    
    function count_detail_barang_by_kategoribarang_cd($params) {
        $sql = "SELECT COUNT(*) 'total'
                FROM barang a
                JOIN ruang b ON a.ruang_cd=b.ruang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd AND a.kategoribarang_cd=d.kategoribarang_cd
                JOIN merk e ON a.merk_cd=e.merk_cd AND a.kategoribarang_cd=e.kategoribarang_cd
                LEFT JOIN lokasi f ON a.lokasi_cd = f.lokasi_cd
                WHERE a.kategoribarang_cd LIKE ? AND a.jenisbarang_cd LIKE ? 
                AND a.merk_cd LIKE ? AND a.ruang_cd LIKE ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }
    
    function get_detail_barang_by_kategoribarang_cd($params) {
        $sql = "SELECT a.*, b.ruang_name, c.kategoribarang_name, 
                d.jenisbarang_name, e.merk_name, f.lokasi_name
                FROM barang a
                JOIN ruang b ON a.ruang_cd =b.ruang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd AND a.kategoribarang_cd=d.kategoribarang_cd
                JOIN merk e ON a.merk_cd=e.merk_cd AND a.kategoribarang_cd=e.kategoribarang_cd
                LEFT JOIN lokasi f ON a.lokasi_cd = f.lokasi_cd
                WHERE a.kategoribarang_cd LIKE ? AND a.jenisbarang_cd LIKE ? 
                AND a.merk_cd LIKE ? AND a.ruang_cd LIKE ?
                ORDER BY a.barang_id DESC               
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function get_all_barang_by_kategoribarang_cd($params) {
        $sql = "SELECT a.*, b.ruang_name, c.kategoribarang_name, 
                d.jenisbarang_name, e.merk_name, f.lokasi_name
                FROM barang a
                JOIN ruang b ON a.ruang_cd =b.ruang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd=c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd AND a.kategoribarang_cd=d.kategoribarang_cd
                JOIN merk e ON a.merk_cd=e.merk_cd AND a.kategoribarang_cd=e.kategoribarang_cd
                LEFT JOIN lokasi f ON a.lokasi_cd = f.lokasi_cd
                WHERE a.kategoribarang_cd LIKE ? AND a.jenisbarang_cd LIKE ? 
                AND a.merk_cd LIKE ? AND a.ruang_cd LIKE ?
                ORDER BY a.barang_id DESC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function count_detail_barang_by_lokasi_cd($params) {
        $sql = "SELECT COUNT(*) 'total'
                FROM barang a
                JOIN ruang b ON a.ruang_cd=b.ruang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd AND a.kategoribarang_cd=d.kategoribarang_cd
                JOIN merk e ON a.merk_cd=e.merk_cd AND a.kategoribarang_cd=e.kategoribarang_cd
                LEFT JOIN lokasi f ON a.lokasi_cd = f.lokasi_cd
                WHERE a.lokasi_cd LIKE ? AND a.kategoribarang_cd LIKE ? AND a.jenisbarang_cd LIKE ? 
                AND a.merk_cd LIKE ? AND a.ruang_cd LIKE ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }
    
    function get_detail_barang_by_lokasi_cd($params) {
        $sql = "SELECT a.*, b.ruang_name, c.kategoribarang_name, 
                d.jenisbarang_name, e.merk_name, f.lokasi_name
                FROM barang a
                JOIN ruang b ON a.ruang_cd =b.ruang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd AND a.kategoribarang_cd=d.kategoribarang_cd
                JOIN merk e ON a.merk_cd=e.merk_cd AND a.kategoribarang_cd=e.kategoribarang_cd
                JOIN lokasi f ON a.lokasi_cd = f.lokasi_cd
                WHERE a.lokasi_cd LIKE ? AND a.kategoribarang_cd LIKE ? AND a.jenisbarang_cd LIKE ? 
                AND a.merk_cd LIKE ? AND a.ruang_cd LIKE ?
                ORDER BY a.barang_id DESC               
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function get_all_barang_by_lokasi_cd($params) {
        $sql = "SELECT a.*, b.ruang_name, c.kategoribarang_name, 
                d.jenisbarang_name, e.merk_name, f.lokasi_name
                FROM barang a
                JOIN ruang b ON a.ruang_cd =b.ruang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd=c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd AND a.kategoribarang_cd=d.kategoribarang_cd
                JOIN merk e ON a.merk_cd=e.merk_cd AND a.kategoribarang_cd=e.kategoribarang_cd
                JOIN lokasi f ON a.lokasi_cd = f.lokasi_cd
                WHERE a.lokasi_cd LIKE ? AND a.kategoribarang_cd LIKE ? AND a.jenisbarang_cd LIKE ? 
                AND a.merk_cd LIKE ? AND a.ruang_cd LIKE ? 
                ORDER BY a.barang_id DESC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function get_all_penyusutan_by_barang_id($params) {
        $sql = "SELECT a.* FROM penyusutan_detail a
                WHERE a.barang_id = ?
                ORDER BY a.penyusutandet_id DESC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get user profil
    function get_all_barang() {
        $sql = "SELECT a.*, b.pengadaan_tgl_dist FROM barang a
                JOIN pengadaan b ON a.pengadaan_id = b.pengadaan_id
                WHERE a.jenisbarang_cd <> 4
                GROUP BY a.barang_id
                ";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get user profil
    function get_all_barang_pagination($params) {
        $sql = "SELECT a.*, b.gpbarang_name, c.kategoribarang_name, d.jenisbarang_name, e.merk_name FROM barang a
                JOIN group_barang b ON a.gpbarang_cd =b.gpbarang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd
                JOIN merk e ON a.merk_cd =e.merk_cd  
                GROUP BY a.barang_id
                ORDER BY a.barang_id DESC                
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    function get_all_barang_pagination_search($params) {
        $sql = "SELECT a.*, b.gpbarang_name, c.kategoribarang_name, d.jenisbarang_name, e.merk_name FROM barang a
                JOIN group_barang b ON a.gpbarang_cd =b.gpbarang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd
                JOIN merk e ON a.merk_cd =e.merk_cd   
                WHERE a.barang_noinventaris LIKE ?
                AND a.barang_nama LIKE ?
                AND a.barang_kondisi LIKE ?
                AND a.barang_status LIKE ?
                AND a.gpbarang_cd LIKE ?
                AND a.jenisbarang_cd LIKE ?
                AND a.kategoribarang_cd LIKE ?
                AND a.merk_cd LIKE ?      
                GROUP BY a.barang_id
                ORDER BY a.barang_id DESC                
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    // get user profil
    function get_barang_by_pengadaan_id_pagination($params) {
        $sql = "SELECT a.*, b.gpbarang_name, c.kategoribarang_name, d.jenisbarang_name, e.merk_name, f.lokasi_name FROM barang a
                JOIN group_barang b ON a.gpbarang_cd =b.gpbarang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd
                JOIN merk e ON a.merk_cd =e.merk_cd
                JOIN lokasi f ON a.lokasi_cd =f.lokasi_cd
                WHERE e.kategoribarang_cd = c.kategoribarang_cd AND a.pengadaan_id = ?
                ORDER BY a.barang_id ASC
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_barang_by_pengadaan_id($params) {
        $sql = "SELECT a.*, b.gpbarang_name, c.kategoribarang_name, d.jenisbarang_name, e.merk_name, f.lokasi_name, g.* FROM barang a
                JOIN group_barang b ON a.gpbarang_cd =b.gpbarang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd
                JOIN merk e ON a.merk_cd =e.merk_cd
                JOIN lokasi f ON a.lokasi_cd =f.lokasi_cd
                LEFT JOIN ruang g ON a.ruang_cd =g.ruang_cd
                WHERE a.pengadaan_id = ?
                GROUP BY a.barang_id
                ORDER BY a.barang_id ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_all_barang_by_pengadaan_id($params) {
        $sql = "SELECT * FROM barang WHERE pengadaan_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_barang_by_id($params) {
        $sql = "SELECT a.*, b.kategoribarang_name, vendor_name, ruang_nomor
                FROM barang a JOIN kategoribarang b ON a.kategoribarang_cd = b.kategoribarang_cd
                JOIN vendor c ON c.vendor_cd = a.vendor_cd
                JOIN ruang d ON d.ruang_cd = a.ruang_cd
                WHERE a.barang_id =  ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_barang_detail_by_id($params) {
        $sql = "SELECT a.*, b.gpbarang_name, c.kategoribarang_name, d.jenisbarang_name, 
                e.merk_name, f.lokasi_name, g.ruang_name FROM barang a
                JOIN group_barang b ON a.gpbarang_cd =b.gpbarang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd AND a.kategoribarang_cd =d.kategoribarang_cd
                JOIN merk e ON a.merk_cd =e.merk_cd AND a.kategoribarang_cd =e.kategoribarang_cd
                JOIN lokasi f ON a.lokasi_cd =f.lokasi_cd
                JOIN ruang g ON a.ruang_cd = g.ruang_cd
                WHERE a.barang_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_barang_by_noinventaris($params) {
        $sql = "SELECT * FROM barang WHERE barang_noinventaris = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function count_barang_by_noinventaris_nama($params) {
        $sql = "SELECT COUNT(barang_id)'total' FROM barang WHERE barang_noinventaris LIKE ? AND barang_nama LIKE ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }
    
    function get_barang_by_noinventaris_nama($params) {
        $sql = "SELECT * FROM barang WHERE barang_noinventaris LIKE ? AND barang_nama LIKE ?
                LIMIT ?,?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    /*
      function get_barang_by_pengadaan_id($params) {
      $sql = "SELECT * FROM barang WHERE pengadaan_id = ?";
      $query = $this->db->query($sql, $params);
      if ($query->num_rows() > 0) {
      $result = $query->row_array();
      $query->free_result();
      return $result;
      } else {
      return array();
      }
      } */

    // insert
    function insert($user_id) {
        // update by this date
        $sql = "INSERT INTO barang( barang_noinventaris, barang_nama, barang_harga_beli, barang_nilai_buku, 
                                    barang_nilai_upgrade, barang_umurekonomis, barang_keterangan, barang_kondisi,
                                    barang_status, barang_tglbeli, barang_tglberakhir, barang_tglpenyusutan, 
                                    barang_garansi, barang_tgl_garansi, barang_pengguna, barang_susut, 
                                    barang_id_lama, barang_id_baru, barang_upgrade, gpbarang_cd, 
                                    vendor_cd, lokasi_cd, ruang_cd, kategoribarang_cd, jenisbarang_cd, 
                                    merk_cd, pengadaan_id, pengajuanbarang_id, mdb, mdd)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $user_id);
    }

    // edit
    function edit($user_id) {
        // update by this date
        // $sql = "UPDATE barang SET barang_noinventaris = ?, barang_nama = ?, barang_harga = ?, gpbarang_cd = ?, kategoribarang_cd = ?, jenisbarang_cd = ?, barang_umurekonomis = ?, merk_cd = ?, mdb = ? WHERE barang_id = ?";
        $sql = "UPDATE barang SET   barang_noinventaris = ?, 
                                    barang_nama = ?, 
                                    barang_harga_beli = ?, 
                                    barang_nilai_buku = ?, 
                                    barang_nilai_upgrade = ?, 
                                    barang_umurekonomis = ?, 
                                    barang_keterangan = ?, 
                                    barang_kondisi = ?,
                                    barang_status = ?, 
                                    barang_tglbeli = ?, 
                                    barang_tglberakhir = ?, 
                                    barang_tglpenyusutan = ?, 
                                    barang_garansi = ?, 
                                    barang_tgl_garansi = ?, 
                                    barang_pengguna = ?, 
                                    barang_susut = ?, 
                                    barang_id_lama = ?, 
                                    barang_id_baru = ?, 
                                    barang_upgrade = ?, 
                                    gpbarang_cd = ?, 
                                    vendor_cd = ?, 
                                    lokasi_cd = ?, 
                                    ruang_cd = ?, 
                                    kategoribarang_cd = ?, 
                                    jenisbarang_cd = ?, 
                                    merk_cd = ?, 
                                    pengadaan_id = ?, 
                                    pengajuanbarang_id = ?, 
                                    mdb = ?  
                                    WHERE barang_id = ?";
        return $this->db->query($sql, $user_id);
    }

    function edit_barang_tglpenyusutan_tglpenyusutanbarang_nilai_buku($user_id) {
        // update by this date
        $sql = "UPDATE barang SET barang_tglpenyusutan = ?, barang_nilai_buku = ? WHERE barang_id = ?";
        return $this->db->query($sql, $user_id);
    }

    // delete
    function delete($user_id) {
        // update by this date
        $sql = "DELETE FROM barang WHERE barang_id = ?";
        return $this->db->query($sql, $user_id);
    }

    function delete_by_pengadaan_id($user_id) {
        // update by this date
        $sql = "DELETE FROM barang WHERE pengadaan_id = ?";
        return $this->db->query($sql, $user_id);
    }

    /*
     * LAPORAN
     */

    function get_barang_by_search_pagination($param) {
        $sql = "SELECT * FROM barang a
                WHERE a.jenisbarang_cd LIKE ? AND a.lokasi_cd LIKE ? AND a.merk_cd LIKE ? LIMIT ?, ?";
        $query = $this->db->query($sql, $param);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_count_barang_by_search($param) {
        $sql = "SELECT COUNT(*) AS 'total' FROM barang a
                WHERE a.jenisbarang_cd LIKE ? AND a.lokasi_cd LIKE ? AND a.merk_cd LIKE ?";
        $query = $this->db->query($sql, $param);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    function get_total_barang() {
        $sql = "SELECT COUNT(b.barang_id) AS 'total_barang', 
                SUM(b.barang_harga_beli) AS 'total_harga_beli', 
                SUM(b.barang_nilai_buku) AS 'total_nilai_buku'
                FROM barang b
                ";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    function get_barang_by_kategoribarang_cd($param) {
        $sql = "SELECT a.*, 
                COUNT(b.barang_id) AS 'total_barang', 
                SUM(b.barang_harga_beli) AS 'total_harga_beli', 
                SUM(b.barang_nilai_buku) AS 'total_nilai_buku'  
                FROM jenisbarang a
                LEFT JOIN barang b ON a.jenisbarang_cd = b.jenisbarang_cd
                WHERE a.kategoribarang_cd = ?
                GROUP BY a.jenisbarang_cd ";
        $query = $this->db->query($sql, $param);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    // get user profil
    function get_all_barang_penyusutan() {
        $sql = "SELECT *, DATE(mdd)'tanggal' FROM barang WHERE barang_susut = '1'";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

}
